# -*- coding: utf-8 -*-

import sqlalchemy as sa
from sqlalchemy import event

from bopress import metas, options
from bopress import settings
from bopress.log import Logger
from bopress.model import Users, ObjectPermissions, UserObjectPermissions, UserRole
from bopress.orm import SessionFactory, get_ref_column, pk
from bopress.utils import Utils, DataResult

__author__ = 'yezang'


def login(user_key, user_pass):
    """
    Valid user logon.
    :param user_key: value in [user_login, user_email, user_mobile_phone]
    :param user_pass: password
    :return: `.model.Users`
    """
    r = DataResult()
    r.success = False
    r.message = "401"
    if not user_key or not user_pass:
        return r
    s = SessionFactory.session()
    u = s.query(Users).filter(
        sa.or_(Users.user_login == user_key, Users.user_email == user_key, Users.user_mobile_phone == user_key)) \
        .filter(Users.user_pass == Utils.md5(user_pass)) \
        .filter(Users.user_status == 1).one_or_none()
    if u:
        r.success = True
        r.message = "200"
        r.data = u
        return r
    return r


def get(user_id=None, user_login="", user_email="", user_mobile_phone=""):
    """
    Get a user, any of user_id, user_login, user_email, user_mobile_phone is not empty.

    :param user_id: user id
    :param user_login: user login
    :param user_email: email
    :param user_mobile_phone: mobile phone
    :return: `.model.Users`
    """
    s = SessionFactory.session()
    if user_id:
        return s.query(Users).filter(Users.user_id == user_id).one_or_none()
    if user_login:
        return s.query(Users).filter(Users.user_login == user_login).one_or_none()
    if user_email:
        return s.query(Users).filter(Users.user_email == user_email).one_or_none()
    if user_mobile_phone:
        return s.query(Users).filter(Users.user_mobile_phone == user_mobile_phone).one_or_none()
    return None


def get_user_id(user_id=None, user_login="", user_email="", user_mobile_phone=""):
    """
    Get a user primary key, any of user_id, user_login, user_email, user_mobile_phone is not empty.

    :param user_id: user id
    :param user_login: user login
    :param user_email: email
    :param user_mobile_phone: mobile phone
    :return: bopress.model.Users
    """
    s = SessionFactory.session()
    if user_login:
        return s.query(Users.user_id).filter(Users.user_login == user_login).scalar()
    if user_email:
        return s.query(Users.user_id).filter(Users.user_email == user_email).scalar()
    if user_mobile_phone:
        return s.query(Users.user_id).filter(Users.user_mobile_phone == user_mobile_phone).scalar()
    return user_id


def get_user_status(user_id=None, user_login="", user_email="", user_mobile_phone=""):
    s = SessionFactory.session()
    if user_id:
        return s.query(Users.user_status).filter(Users.user_id == user_id).scalar()
    if user_login:
        return s.query(Users.user_status).filter(Users.user_login == user_login).scalar()
    if user_email:
        return s.query(Users.user_status).filter(Users.user_email == user_email).scalar()
    if user_mobile_phone:
        return s.query(Users.user_status).filter(Users.user_mobile_phone == user_mobile_phone).scalar()
    return 0


def get_super_user_id():
    """
    Get site super primary key.
    :return: str
    """
    return options.get_options("bo_super_user", None)


def get_user_roles(user_id=None, user_login="", user_email="", user_mobile_phone=""):
    if not user_id:
        user_id = get_user_id(user_id, user_login, user_email, user_mobile_phone)
    return metas.get_user_metas(user_id, "bo_roles")


def save_user_roles(user_id, roles, create=False):
    """
    Save user roles.

    :param user_id: user primary key.
    :param roles: role set.
    :param create: ``True`` is create, ``False`` is update. default ``False``.
    :return:
    """
    if not roles:
        return
    metas.save_user_metas(user_id, "bo_roles", roles)
    s = SessionFactory.session()
    # if update, delete all roles and reinstall all.
    if not create:
        s.query(UserRole).filter(UserRole.user_id == user_id).delete()
    for role in roles:
        s.add(UserRole(role_id=pk(), user_id=user_id, role_name=role))
    s.commit()


def registry(user_login, user_pass, user_email="", user_mobile_phone="", user_nicename="", display_name="",
             user_status=0, roles=list()):
    """
    Registry New User

    :param list roles: user roles.
    :param str user_login: user login account
    :param str user_pass: password
    :param str user_email: email
    :param str user_mobile_phone: mobile phone
    :param str user_nicename: personal space name
    :param str display_name: nick name
    :param int user_status: ``1`` enable, ``0`` disabled.
    :return: `.utils.DataResult`
    """
    r = DataResult()
    r.success = False
    if not user_login or not user_pass or not user_email:
        return r
    s = SessionFactory.session()
    num = s.query(Users).filter(Users.user_login == user_login).count()
    if num > 0:
        r.message = "UserLoginExists"
        return r
    if user_email:
        num = s.query(Users).filter(Users.user_email == user_email).count()
        if num > 0:
            r.message = "UserEmailExists"
            return r
    u = Users()
    try:
        u.user_id = pk()
        u.user_login = user_login
        u.user_pass = Utils.md5(user_pass)
        u.user_email = user_email
        u.user_mobile_phone = user_mobile_phone
        u.user_nicename = user_nicename
        u.display_name = display_name
        u.user_status = user_status
        u.user_activation_key = pk()
        u.user_registered = Utils.current_datetime()
        s.add(u)
        s.commit()
        site_options = options.get_site_options()
        default_roles = set(site_options.get("default_role", False, "subscriber"))
        # for create user.
        if roles:
            default_roles = set(roles)
        save_user_roles(u.user_id, default_roles, True)
        metas.save_user_metas(u.user_id, "bo_super", False)
        metas.save_user_metas(u.user_id, "bo_description", "")
        metas.save_user_metas(u.user_id, "bo_gravatar", "")
        r.success = True
        r.message = "200"
        r.data = u
        return r
    except Exception as e:
        Logger.exception(e)
        r.success = False
        r.message = "500"
        r.data = None
        return r


def update(user_id, user_login, user_email="", user_mobile_phone="", user_nicename="", display_name="",
           user_status=0, roles=list(), description="", gravatar=""):
    """
    Update a User

    :param str description: personal description.
    :param str gravatar: avatar
    :param list roles: user roles
    :param str user_id: user primary key.
    :param str user_login: account
    :param str user_email: email
    :param str user_mobile_phone: mobile phone
    :param str user_nicename: personal space name
    :param str display_name: nick name
    :param int user_status: ``1`` enable, ``0`` disabled.
    :return: `.utils.DataResult`
    """
    r = DataResult()
    r.success = False
    if not user_email:
        return r
    s = SessionFactory.session()
    num = s.query(Users).filter(sa.and_(Users.user_login == user_login, Users.user_id != user_id)).count()
    if num > 0:
        r.message = "UserLoginExists"
        return r
    if user_email:
        num = s.query(Users).filter(sa.and_(Users.user_email == user_email, Users.user_id != user_id)).count()
        if num > 0:
            r.message = "UserEmailExists"
            return r
    if user_mobile_phone:
        num = s.query(Users).filter(sa.and_(Users.user_mobile_phone == user_mobile_phone, Users.user_id != user_id)) \
            .count()
        if num > 0:
            r.message = "UserMobilePhoneExists"
            return r
    if user_nicename:
        num = s.query(Users).filter(sa.and_(Users.user_nicename == user_nicename, Users.user_id != user_id)).count()
        if num > 0:
            r.message = "UserNiceNameExists"
            return r
    if display_name:
        num = s.query(Users).filter(sa.and_(Users.display_name == display_name, Users.user_id != user_id)).count()
        if num > 0:
            r.message = "UserDisplayNameExists"
            return r
    u = s.query(Users).get(user_id)
    if u:
        try:
            if user_login:
                u.user_login = user_login
            u.user_email = user_email
            u.user_mobile_phone = user_mobile_phone
            u.user_nicename = user_nicename
            u.display_name = display_name
            u.user_status = user_status
            s.commit()
            if roles:
                roles = set(roles)
            else:
                roles = set()
            save_user_roles(u.user_id, roles)
            metas.save_user_metas(u.user_id, "bo_description", description)
            metas.save_user_metas(u.user_id, "bo_gravatar", gravatar)
            r.success = True
            r.message = "200"
            r.data = u
            return r
        except Exception as e:
            Logger.exception(e)
            r.success = False
            r.message = "500"
            r.data = None
            return r
    else:
        r.success = False
        r.message = "404"
        r.data = None
        return r


def remove_capabilitys(caps):
    """
    Bulk remove capabilitys
    :param caps: list or tuple, ['perm1','perm2','perm3'..]
    :return: boolean
    """
    c = options.get_options("bo_capabilities")
    if not c:
        return False
    for perm in caps:
        if perm in c:
            del c[perm]
            # delete in roles.
            roles = options.get_options("bo_roles")
            if roles:
                for role_item in roles.values():
                    if perm in role_item[1]:
                        role_item[1].remove(perm)
    return True


def add_capabilitys(caps):
    c = options.get_options("bo_capabilities")
    if not c:
        return
    for perm in caps:
        # perm[0] is cap name
        # perm[1] is cap group
        # perm[2] is cap description
        c[perm[0]] = (perm[1], perm[2])
    options.save_options("bo_capabilities", c)


def add_capability(name, group="general", description=""):
    """
    Add a capability.

    :param name: capability name
    :param group: capability group only for role manage.
    :param description: capability description.
    :return:
    """
    add_capabilitys(((name, group, description),))


def add_data_capabilitys(caps):
    c = options.get_options("bo_capabilities")
    if not c:
        return
    for perm in caps:
        c['d_{0}'.format(perm[0])] = (perm[1], perm[2])
    options.save_options("bo_capabilities", c)


def add_data_capability(name, group="general", description=""):
    """
    Add data capability for orm CURD. can also be use for whole site. but not recommended.
    Capability startswith ``d_``.

    :param name: capability name
    :param group: capability group only for role manage.
    :param description: capability description
    """
    add_data_capabilitys(((name, group, description),))


def add_role_capabilitys(caps):
    c = options.get_options("bo_capabilities")
    if not c:
        return
    for perm in caps:
        c['r_{0}'.format(perm[0])] = (perm[1], perm[2])
    options.save_options("bo_capabilities", c)


def add_role_capability(name, group="general", description=""):
    """
    Add role capability for whole site or CURD.
    Capability startswith ``r_``.

    :param name: capability name
    :param group: capability group only for role manage.
    :param description: capability description
    """
    add_role_capabilitys(((name, group, description),))


def add_role(name, display_name="", capabilities=None):
    """
    Add a role.
    :param str name: role name, en.
    :param str display_name: role display name, zh.
    :param list capabilities: capability set.
    :return:
    """
    if type(capabilities) is not list:
        print("capabilities require list type")
        return
    c = options.get_options("bo_roles")
    if not c:
        return
    if not display_name:
        display_name = name
    if not capabilities:
        capabilities = set()
    c[name] = [display_name, capabilities]
    options.save_options("bo_roles", c)


def get_all_roles():
    """
    Get site all roles.
    :return:
    """
    return options.get_options("bo_roles")


class Auth(object):
    def __init__(self, user_id=None, user_login="", user_email="", user_mobile_phone=""):
        self._user_id = get_user_id(user_id, user_login, user_email, user_mobile_phone)

    def has_perms(self, caps=""):
        """

        :param caps: str or list or set
        :return: bool
        """
        if not self._user_id:
            return False
        if caps is str:
            caps = [caps]
        perms = set()
        roles = metas.get_user_metas(self._user_id, "bo_roles")
        platform_roles = get_all_roles()
        for role in platform_roles:
            if role in roles:
                perms |= set(platform_roles[role][1])
        caps = set(caps)
        if caps.issubset(perms):
            return True
        return False

    def get_perms(self):
        perms = set()
        if not self._user_id:
            return perms
        if self.is_super():
            return Auth.get_all_perms()
        roles = metas.get_user_metas(self._user_id, "bo_roles")
        if not roles:
            return perms
        platform_roles = options.get_options("bo_roles")
        for role in platform_roles:
            if role in roles:
                perms |= set(platform_roles[role][1])
        return perms

    @staticmethod
    def get_all_perms():
        caps = options.get_options('bo_capabilities')
        return set(caps.keys())

    def is_super(self):
        if not self._user_id:
            return False
        return metas.get_user_metas(self._user_id, "bo_super")


class ObjectPermissionChecker(object):
    @staticmethod
    def _filter_role_perms(perms):
        tmp = set()
        for p in perms:
            if p.startswith("r_"):
                tmp.add(p)
        return tmp

    @staticmethod
    def _filter_data_perms(perms):
        tmp = set()
        for p in perms:
            if p.startswith("d_"):
                tmp.add(p)
        return tmp

    @staticmethod
    def klass_name(model_object):
        return "{0}.{1}".format(model_object.__class__.__module__, model_object.__class__.__name__)

    @staticmethod
    def get_all_perms(model_object=None):
        """
        get all perms of object, data perms and role perms. user_id does not participate.

        :param model_object: object instance with pk value.
        :return: data perms and role perms
        """
        perms = set()
        if not model_object:
            return perms
        pk_value = getattr(model_object, get_ref_column(type(model_object)).key)
        if not pk_value:
            Logger.error("Model instance pk value None!")
            return perms
        klass_full_name = ObjectPermissionChecker.klass_name(model_object)
        s = SessionFactory.session()
        q = s.query(UserObjectPermissions.perm) \
            .select_from(ObjectPermissions).outerjoin(UserObjectPermissions) \
            .filter(UserObjectPermissions.cls_name == klass_full_name) \
            .filter(ObjectPermissions.object_id == str(pk_value))
        for perm in q.all():
            perms.add(perm[0])
        return perms

    @staticmethod
    def get_user_perms(user_id, model_object=None):
        """
        if user_id is not none return data perms, otherwise return role perms.

        :param user_id: user primary key value.
        :param model_object: object instance with pk value.
        :return: data perms or role perms
        """
        perms = set()
        if not model_object or not user_id:
            return perms

        all_perms = ObjectPermissionChecker.get_all_perms(model_object)
        auth = Auth(user_id)
        if auth.is_super():
            return all_perms
        else:
            self_perms = auth.get_perms()
            same_perms = self_perms & all_perms
            return same_perms

    @staticmethod
    def perms_startswith(prefix, perms):
        for p in perms:
            if not p.startswith(prefix):
                return False
        return True

    @staticmethod
    def _create_user_object_permission(m):
        """
        Create user object permission relationship.

        :param UserObjectPermissions m: instance
        :return: UserObjectPermissions instance
        """
        s = SessionFactory.session()
        uop = s.query(UserObjectPermissions) \
            .filter(UserObjectPermissions.cls_name == m.cls_name) \
            .filter(UserObjectPermissions.perm == m.perm) \
            .filter(UserObjectPermissions.user_id == m.user_id).scalar()

        if uop:
            return uop.user_object_permission_id

        s.add(m)
        s.commit()
        return m.user_object_permission_id

    @staticmethod
    def assign(caps="", model_object=None, user_id=None):
        """
        assgin perms for model object, if ``user_id`` is none save role perms, otherwise data perms.
        if ``user_id`` is none, all perms require startswith ``r_``. role perms.
        if ``user_id`` is not none, all perms require startswith ``d_``. data perms.
        if ``model_object`` of ``user_id`` need assign to another user, caps require data perms. startswith ``d_``.

        :param user_id:
        :param caps: str or list
        :param model_object: instance with pk value.
        :return:
        """
        if not caps or not model_object:
            return False
        if type(caps) is str:
            caps = [caps]
        # is data perms or role perms
        if user_id:
            # user_id is not none, perm need startswith ``d_``
            if not ObjectPermissionChecker.perms_startswith('d_', caps):
                Logger.error("user_id is not none, perm need startswith ``d_``!")
                return False
        else:
            # user_id is none, perm need startswith ``r_``
            if not ObjectPermissionChecker.perms_startswith('r_', caps):
                Logger.error("user_id is none, perm need startswith ``r_``!")
                return False
        pk_v = getattr(model_object, get_ref_column(type(model_object)).key)
        if not pk_v:
            Logger.error("Model instance pk value None!")
            return False
        klass_full_name = ObjectPermissionChecker.klass_name(model_object)
        s = SessionFactory.session()
        op_items = list()
        for cap in caps:
            uop = UserObjectPermissions()
            uop.user_object_permission_id = pk()
            uop.user_id = user_id
            uop.cls_name = klass_full_name
            uop.perm = cap
            user_object_permission_id = ObjectPermissionChecker._create_user_object_permission(uop)
            op_pk_v = str(pk_v)
            num = s.query(ObjectPermissions).filter(ObjectPermissions.object_id == op_pk_v) \
                .filter(ObjectPermissions.user_object_permission_id == user_object_permission_id).count()
            if num == 0:
                # create object permission.
                op = ObjectPermissions()
                op.object_permission_id = pk()
                op.object_id = op_pk_v
                op.user_object_permission_id = user_object_permission_id
                op_items.append(op)
        s.add_all(op_items)
        s.commit()
        return True

    @staticmethod
    def has_perms(user_id, caps="", model_object=None):
        """
        user has perms on model_object, require user_id participate.

        :param user_id:
        :param caps: str or list permissions
        :param model_object: instance with pk value.
        :return: bool
        """
        if not user_id:
            return False
        auth = Auth(user_id)
        if auth.is_super():
            return True
        user_perms = ObjectPermissionChecker.get_user_perms(user_id, model_object)
        if type(caps) is str:
            return caps in user_perms
        return user_perms.issuperset(set(caps))

    @staticmethod
    def remove_perms(caps="", model_object=None, user_id=None):
        """
        remove permissions from model_object, if user_id is none remove role perms, otherwise remove role perms.

        :param user_id: user primary key value.
        :param caps: permissions
        :param model_object: instance with pk value.
        :return: int effect records
        """
        pk_value = getattr(model_object, get_ref_column(type(model_object)).key)
        if not pk_value:
            Logger.error("Model instance pk value None!")
            return 0

        klass_full_name = ObjectPermissionChecker.klass_name(model_object)

        s = SessionFactory.session()

        effect_num = 0

        q = s.query(ObjectPermissions).outerjoin(UserObjectPermissions) \
            .filter(UserObjectPermissions.cls_name == klass_full_name) \
            .filter(ObjectPermissions.object_id == str(pk_value)) \
            .filter(UserObjectPermissions.user_id == user_id)

        if type(caps) is str:
            q = q.filter(UserObjectPermissions.perm == caps)
        else:
            q = q.filter(UserObjectPermissions.perm.in_(caps))

        for op in q.all():
            s.delete(op)
            effect_num += 1
        s.commit()

        return effect_num

    @staticmethod
    def get_roles(model_object=None, use_subquery=True):
        """
        get all roles of model object.

        :param model_object:
        :param use_subquery:
        :return:
        """

        role_perms = ObjectPermissionChecker._filter_role_perms(ObjectPermissionChecker.get_all_perms(model_object))
        platform_roles = options.get_options("bo_roles")
        roles = set()
        for k, v in platform_roles.items():
            # k is role name, v[1] is perms.
            same_caps = set(v[1]) & role_perms
            if same_caps:
                roles.add(k)

        s = SessionFactory.session()
        q = s.query(UserRole).filter(UserRole.role_name.in_(roles))
        if use_subquery:
            return q.subquery()
        return q.all()

    @staticmethod
    def get_owners(model_object=None, use_subquery=True):

        """
        get all owners of model object.

        :param model_object:
        :param use_subquery:
        :return:
        """
        pk_value = getattr(model_object, get_ref_column(type(model_object)).key)
        if not pk_value:
            Logger.error("Model instance pk value None!")
            return None

        klass_full_name = ObjectPermissionChecker.klass_name(model_object)

        s = SessionFactory.session()
        q = s.query(Users) \
            .select_from(ObjectPermissions).outerjoin(UserObjectPermissions) \
            .outerjoin(Users, Users.user_id == UserObjectPermissions.user_id) \
            .filter(UserObjectPermissions.cls_name == klass_full_name) \
            .filter(ObjectPermissions.object_id == str(pk_value))
        q = q.filter(UserObjectPermissions.user_id.isnot(None)).group_by(UserObjectPermissions.user_id)

        if use_subquery:
            return q.subquery()

        return q.all()

    @staticmethod
    def get_users(model_object=None, use_subquery=True):
        """
        get all users of model object, owner and role users
        :param model_object:
        :param use_subquery:
        :return:
        """
        owners = ObjectPermissionChecker.get_owners(model_object)
        roles_subq = ObjectPermissionChecker.get_roles(model_object)

        s = SessionFactory.session()

        users = s.query(Users).select_from(roles_subq) \
            .outerjoin(Users, roles_subq.c.user_id == Users.user_id).subquery()

        q = s.query(users.c.Users).select_from(users).union(owners)

        if use_subquery:
            return q.subquery()

        return q.all()

    @staticmethod
    def get_objects(user_id, caps="", klass=None, union=True, any_perm=False):
        """
        获取受权限保护的对象子查询,这个子查询仅返回一列``object_id``

            example::
                p = ObjectPermissionChecker(user_id=2)
                subq = p.get_objects(["edit_post", "r_edit_post"], Posts)
                s = SessionFactory.session()
                q = s.query(Posts.title, Posts.content)\
                        .select_from(subq).outerjoin(Posts, Posts.post_id == subq.c.object_id)
                print(q.all())

        :param user_id:
        :param caps: `str` or `list`, 权限项或者权限项数组
        :param class klass: 数据模型类
        :param bool union: ``True`` 返回用户拥有的`klass`全部数据对象,自己创建的或者他人创建的, ``False``返回取决后面两参数值.
        :param bool any_perm: ``True`` 接受任意权限,即查询不经过权限项过滤
        :return: ``subquery`` or ``None``
        """
        if not caps or not klass or not user_id:
            return None

        pk_column = get_ref_column(klass)
        klass_full_name = "{0}.{1}".format(klass.__module__, klass.__name__)

        s = SessionFactory.session()

        auth = Auth(user_id)
        if auth.is_super() or any_perm:
            # return all.
            return s.query(sa.cast(ObjectPermissions.object_id, pk_column.type).label("object_id")) \
                .select_from(ObjectPermissions) \
                .outerjoin(UserObjectPermissions).filter(UserObjectPermissions.cls_name == klass_full_name) \
                .group_by(ObjectPermissions.object_id) \
                .subquery()

        user_perms = auth.get_perms()
        if type(caps) is str:
            perms = {caps}
        else:
            perms = set(caps)
        valid_perms = user_perms & perms
        if not valid_perms:
            return None

        if union:
            # accept caps startswith `r_`
            valid_role_perms = ObjectPermissionChecker._filter_role_perms(valid_perms)
            # accept caps startswith `d_`
            valid_data_perms = ObjectPermissionChecker._filter_data_perms(valid_perms)

            if not valid_data_perms and not valid_role_perms:
                return None

            all_q = s.query(sa.cast(ObjectPermissions.object_id, pk_column.type).label("object_id")) \
                .select_from(ObjectPermissions) \
                .outerjoin(UserObjectPermissions) \
                .filter(UserObjectPermissions.cls_name == klass_full_name)

            # current user no role caps
            if not valid_role_perms:
                # only one cap
                if len(valid_data_perms) == 1:
                    all_q = all_q.filter(sa.and_(UserObjectPermissions.user_id == user_id,
                                                 UserObjectPermissions.perm == list(valid_data_perms)[0]))
                else:
                    all_q = all_q.filter(sa.and_(UserObjectPermissions.user_id == user_id,
                                                 UserObjectPermissions.perm.in_(valid_data_perms)))
            else:
                if valid_data_perms:
                    all_q = all_q.filter(sa.or_(sa.and_(UserObjectPermissions.user_id == user_id,
                                                        UserObjectPermissions.perm.in_(valid_perms)),
                                                sa.and_(UserObjectPermissions.user_id.is_(None),
                                                        UserObjectPermissions.perm.in_(valid_role_perms))))
                else:
                    all_q = all_q.filter(sa.and_(UserObjectPermissions.user_id.is_(None),
                                                 UserObjectPermissions.perm.in_(valid_role_perms)))

            return all_q.group_by(ObjectPermissions.object_id).subquery()

        if user_id:
            valid_data_perms = ObjectPermissionChecker._filter_data_perms(valid_perms)
            if not valid_data_perms:
                return None

            # user objects
            user_q = s.query(sa.cast(ObjectPermissions.object_id, pk_column.type).label("object_id")) \
                .select_from(ObjectPermissions) \
                .outerjoin(UserObjectPermissions) \
                .filter(UserObjectPermissions.cls_name == klass_full_name) \
                .filter(UserObjectPermissions.user_id == user_id)
            # only one cap
            if len(valid_data_perms) == 1:
                user_q = user_q.filter(UserObjectPermissions.perm == list(valid_data_perms)[0])
            else:
                user_q = user_q.filter(UserObjectPermissions.perm.in_(valid_data_perms))
            return user_q.group_by(ObjectPermissions.object_id).subquery()
        else:
            # accept caps startswith `r_`
            valid_role_perms = ObjectPermissionChecker._filter_role_perms(valid_perms)
            if not valid_role_perms:
                return None

            role_q = s.query(sa.cast(ObjectPermissions.object_id, pk_column.type).label("object_id")) \
                .outerjoin(UserObjectPermissions) \
                .filter(UserObjectPermissions.cls_name == klass_full_name) \
                .filter(UserObjectPermissions.user_id.is_(None))

            # only one cap
            if len(valid_role_perms) == 1:
                role_q = role_q.filter(UserObjectPermissions.perm == list(valid_role_perms)[0])
            else:
                role_q = role_q.filter(UserObjectPermissions.perm.in_(valid_role_perms))

            return role_q.group_by(ObjectPermissions.object_id).subquery()


bo_opchecker = ObjectPermissionChecker


def remove_perms_for_bulk_delete(session, entity_cls, object_id_set):
    """
    批量删除对象权限
    :param session: `.orm.SessionFactory`
    :param class entity_cls: 实体类
    :param object_id_set: list or set
    """
    klass_full_name = "{0}.{1}".format(entity_cls.__module__, entity_cls.__name__)
    a = session.query(ObjectPermissions.object_permission_id) \
        .outerjoin(UserObjectPermissions) \
        .filter(UserObjectPermissions.cls_name == klass_full_name) \
        .filter(ObjectPermissions.object_id.in_(object_id_set)).all()
    items = [i[0] for i in a]
    if items:
        session.query(ObjectPermissions).filter(ObjectPermissions.object_permission_id.in_(set(items))).delete(False)


def remove_perms_on_delete():
    """
        单对象权限关联删除实体类包装器

        用下面的方式删除对象时触发删除对象关联的权限::

            s = SessionFactory.session()
            s.delete(someobject)
            s.commit()

        如果是批量删除::

            s.query(Posts).filter(Posts.post_id == 2).delete()

        则不会触发, 批量删除可以使用辅助函数 `remove_perms_for_bulk_delete`::

            remove_perms_for_bulk_delete(s, Posts, [2])

    :return:
    """

    def wrapp(cls):
        @event.listens_for(cls, 'after_delete')
        def on_delete(mapper, connection, target):
            entity_cls = type(target)
            klass_full_name = "{0}.{1}".format(entity_cls.__module__, entity_cls.__name__)
            pk_val = getattr(target, get_ref_column(entity_cls).key)
            t_object_permissions = "{0}objectpermissions".format(settings.TABLE_NAME_PREFIX)
            t_user_object_permissions = "{0}userobjectpermissions".format(settings.TABLE_NAME_PREFIX)
            sql = """
            SELECT {0}.object_permission_id FROM {1} LEFT JOIN {2}
             ON {3}.user_object_permission_id={4}.user_object_permission_id
              WHERE {5}.cls_name=? AND {6}.object_id=?
            """.format(t_object_permissions, t_object_permissions, t_user_object_permissions,
                       t_user_object_permissions, t_object_permissions,
                       t_user_object_permissions, t_object_permissions)
            r = connection.execute(sql, klass_full_name, pk_val)
            arr = [i[0] for i in r.fetchall()]
            del_sql = """
            DELETE FROM {0} WHERE {1}.object_permission_id=?""" \
                .format(t_object_permissions, t_object_permissions)
            for object_permission_id in arr:
                connection.execute(del_sql, object_permission_id)

        return cls

    return wrapp
